<?php

class m111114_134131_create_database extends CDbMigration
{

	public static $time = 111114;

	public function up()
	{	
		$p = $this->getDbConnection()->tablePrefix;

		# Create Table 'files'
		$this->createTable($p.'files', array(
			'id' => 'pk',
			'time_create' => 'timestamp',
			'user_ip' => 'varchar(60) NOT NULL',
			'user_id' => 'int(11) NOT NULL',
			'md5' => 'varchar(32) NOT NULL',
			'name' => 'varchar(255) NOT NULL',
			'code' => 'varchar(255) NOT NULL',
			'path' => 'varchar(255) NOT NULL',
			'size' => 'int(11) NOT NULL',
			'mime' => 'varchar(60) NOT NULL',
		));
		$this->createIndex('searchindex', $p.'files', 'md5');

		# Create Table 'task_comments'
		$this->createTable($p.'task_comments', array(
			'id' => 'pk',
			'time_create' => 'timestamp',
			'user_id' => 'int(11) NOT NULL',
			'task_id' => 'int(11) NOT NULL',
			'parent_id' => 'int(11) NOT NULL',
			'text' => 'text',
		));
		$this->createIndex('searchindex', $p.'task_comments', 'parent_id, user_id, task_id');

		# Create Table 'task_files'
		$this->createTable($p.'task_files', array(
			'id' => 'pk',
			'task_id' => 'int(11) NOT NULL',
			'file_id' => 'int(11) NOT NULL',
		));
		$this->createIndex('searchindex', $p.'task_files', 'task_id, file_id');

		# Create Table 'task_projects'
		$this->createTable($p.'task_projects', array(
			'id' => 'pk',
			'time_create' => 'timestamp',
			'name' => 'varchar(255) NOT NULL',
			'code' => 'varchar(255) NOT NULL',
		));
		$this->createIndex('searchindex', $p.'task_projects', 'code');

		# Create Table 'task_projects_user'
		$this->createTable($p.'task_projects_user', array(
			'id' => 'pk',
			'user_id' => 'int(11) NOT NULL',
			'project_id' => 'int(11) NOT NULL',
		));
		$this->createIndex('searchindex', $p.'task_projects_user', 'user_id, project_id');

		# Create Table 'task_responsible_users'
		$this->createTable($p.'task_responsible_users', array(
			'id' => 'pk',
			'user_id' => 'int(11) NOT NULL',
			'task_id' => 'int(11) NOT NULL',
		));
		$this->createIndex('searchindex', $p.'task_responsible_users', 'user_id, task_id');

		# Create Table 'task_time'
		$this->createTable($p.'task_time', array(
			'id' => 'pk',
			'time_start' => 'datetime',
			'time_end' => 'datetime',
			'time_break' => 'int(11) NOT NULL',
			'user_id' => 'int(11) NOT NULL',
			'task_id' => 'int(11) NOT NULL',
			'comment' => 'text',
		));
		$this->createIndex('searchindex', $p.'task_time', 'task_id, user_id');

		# Create Table 'tasks'
		$this->createTable($p.'tasks', array(
			'id' => 'pk',
			'time_create' => 'datetime',
			'user_id' => 'int(11) NOT NULL',
			'project_id' => 'int(11) NOT NULL',
			'status' => 'boolean',
			'archived' => 'boolean',
			'critical' => 'boolean',
			'sort' => 'int(11) NOT NULL',
			'text' => 'text',
		));
		$this->createIndex('searchindex', $p.'tasks', 'user_id, project_id, archived, critical');

		# Create Table 'users'
		$this->createTable($p.'users', array(
			'id' => 'pk',
			'time_reg' => 'datetime',
			'time_login' => 'datetime',
			'active' => 'boolean',
			'role' => 'varchar(20) NOT NULL',
			'nick' => 'varchar(255) NOT NULL',
			'name' => 'varchar(255) NOT NULL',
			'last_name' => 'varchar(255) NOT NULL',
			'email' => 'varchar(255) NOT NULL',
			'password' => 'varchar(50) NOT NULL',
			'check_word' => 'varchar(32) NOT NULL',
			'avatar' => 'varchar(255) NOT NULL',
			'cost_dev' => 'float',
			'cost_support' => 'float',
		));
		$this->createIndex('searchindex', $p.'users', 'email, password');

	}

	public function down()
	{
		$p = $this->getDbConnection()->tablePrefix;
		$this->dropTable($p.'files');
		$this->dropTable($p.'task_comments');
		$this->dropTable($p.'task_files');
		$this->dropTable($p.'task_projects');
		$this->dropTable($p.'task_projects_user');
		$this->dropTable($p.'task_responsible_users');
		$this->dropTable($p.'task_time');
		$this->dropTable($p.'tasks');
		$this->dropTable($p.'users');
	}

	/*
	// Use safeUp/safeDown to do migration with transaction
	public function safeUp()
	{
	}

	public function safeDown()
	{
	}
	*/
}